Imports System.Data.OleDb
Imports DTO

Public Class PhieuThuePHG_Dao

    Inherits AbstractDAO

    Public Sub New()

    End Sub

    Public Function LayBang() As DataTable

        Dim dt As New DataTable()
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From PhieuThuePhong"
        ' B4: Thuc thi chuoi strSQL
        Dim da As New OleDbDataAdapter(strSQL, cn)
        da.FillSchema(dt, SchemaType.Source)
        da.Fill(dt)
        ' B5: Dong ket noi CSDL
        cn.Close()
        Return dt

    End Function

    Public Function LayDanhSach() As IList

        Dim ds As New ArrayList()
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From PhieuThuePhong"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            Dim ptDto As New PhieuThuePHG_Dto
            ptDto.MaPT = dr("MaPT")
            ptDto.MaKH = dr("MaKH")
            ptDto.NgayThue = dr("NgayThue")
            ptDto.MaPHG = dr("MaPHG")
            ds.Add(ptDto)
        End While
        ' B5: Dong ket noi CSDL
        dr.Close()
        cn.Close()
        Return ds

    End Function

    Public Sub CapNhatBang(ByVal dt As DataTable)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From PhieuThuePhong"
        'B4: Thuc thi chuoi strSQL
        Dim da As New OleDbDataAdapter(strSQL, cn)
        Dim cb As New OleDbCommandBuilder(da)
        da.Update(dt)
        ' B5: Dong ket noi CSDL
        cn.Close()

    End Sub

    'Tim kiem phieu theu phogn theo ma phong
    Public Function TimKiem(ByVal mphg As Integer) As PhieuThuePHG_Dto

        Dim ptDto As New PhieuThuePHG_Dto
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From PhieuThuePhong Where MaPHG = ?"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        cmd.Parameters.Add("@MaPHG", OleDbType.Integer)

        cmd.Parameters("@MaPHG").Value = mphg

        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            ptDto = New PhieuThuePHG_Dto()
            ptDto.MaPT = dr("MaPT")
            ptDto.MaKH = dr("MaKH")
            ptDto.NgayThue = dr("NgayThue")
            ptDto.MaPHG = dr("MaPHG")
        End While
        ' B5: Dong ket noi CSDL
        conn.Close()
        Return ptDto

    End Function

    Public Sub Them(ByVal ptDto As PhieuThuePHG_Dto)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = _
        "Insert into PhieuThuePhong( MaKH,NgayThue,MaPHG) values (?, ?, ?)"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        'cmd.Parameters.Add("@MaPT", OleDbType.Integer)
        cmd.Parameters.Add("@MaKH", OleDbType.Integer)
        cmd.Parameters.Add("@NgayThue", OleDbType.Date)
        cmd.Parameters.Add("@MaPHG", OleDbType.Integer)

        cmd.Parameters("@MaKH").Value = ptDto.MaKH
        cmd.Parameters("@NgayThue").Value = ptDto.NgayThue
        cmd.Parameters("@MaPHG").Value = ptDto.MaPHG

        cmd.ExecuteNonQuery()

        strSQL = "Select @@IDENTITY"
        cmd = New OleDbCommand(strSQL, conn)
        ptDto.MaPT = CInt(cmd.ExecuteScalar())

        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub

    Public Sub Xoa(ByVal mp As Integer)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim conn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Delete From PhieuThuePhong Where MaPHG = ?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, conn)
        cmd.Parameters.Add("@MaPHG", OleDbType.Integer)

        cmd.Parameters("@MaPHG").Value = mp

        cmd.ExecuteNonQuery()
        ' B5: Dong ket noi CSDL
        conn.Close()

    End Sub
    Public Function TraCuuPT(ByVal mkh As Integer) As PhieuThuePHG_Dto

        Dim ptDto As New PhieuThuePHG_Dto
        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = "Select * From PhieuThuePhong Where MaKH = ?"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.Parameters.Add("@MaKH", OleDbType.Integer)

        cmd.Parameters("@MaKH").Value = mkh

        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            ptDto = New PhieuThuePHG_Dto()
            ptDto.MaKH = dr("MaPT")
            ptDto.MaKH = dr("MaKH")
            ptDto.NgayThue = dr("NgayThue")
            ptDto.MaPHG = dr("MaPHG")
        End While
        ' B5: Dong ket noi CSDL
        cn.Close()
        Return ptDto

    End Function
    Public Sub Sua(ByVal ptDto As PhieuThuePHG_Dto)

        ' B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As OleDbConnection = Me.ConnectionData()
        ' B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String = _
        "Update PhieuThuePhong Set MaPHG = ? , NgayThue = ? Where MaKH = ?"
        ' B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.Parameters.Add("@MaPHG", OleDbType.Integer)
        cmd.Parameters.Add("@NgayThue", OleDbType.Date)
        cmd.Parameters.Add("@MaKH", OleDbType.Integer)

        cmd.Parameters("@MaPHG").Value = ptDto.MaPHG
        cmd.Parameters("@NgayThue").Value = ptDto.NgayThue
        cmd.Parameters("@MaKH").Value = ptDto.MaKH

        cmd.ExecuteNonQuery()
        ' B5: Dong ket noi CSDL
        cn.Close()

    End Sub
End Class
